clear

********************************************
*Table 4: Control for mayor characteristics
********************************************
use "$data\final.dta"

*copy down information on mayors to non-election or -appointment years
foreach c of varlist beruf_coded beruf2_coded unifach bildung2_coded bildung_coded geburtsdatum mayor_party{
gen `c'_old= `c'
bysort code (year):	replace `c'=`c'[_n-1] if `c'=="" & `c'[_n-1]!=""
	} 

*recode the variables one education, occupation or university majors
replace bildung_coded = "Highereduc" if bildung_coded == "Promotion" | bildung_coded == "Hochschulabschluss"
replace bildung2_coded = "Uni" if bildung2_coded == "Promotion" | bildung2_coded == "Hochschulabschluss"
replace beruf_coded = "other" if beruf_coded == "IT, computer science" | beruf_coded == "artist" | beruf_coded == "butcher" | beruf_coded == "church" | beruf_coded == "health" | beruf_coded == "housework" | beruf_coded == "journalism" | beruf_coded == "member of parliament" | beruf_coded == "military" | beruf_coded == "performing arts" | beruf_coded == "social work" | beruf_coded == "union" | beruf_coded == "unskilled worker"
replace beruf_coded = "business" if beruf_coded == "self-employment" | beruf_coded == "banking" | beruf_coded == "industry"
replace beruf2_coded = "other" if beruf2_coded == "IT, computer science" | beruf2_coded == "artist" | beruf2_coded == "butcher" |beruf2_coded == "church" | beruf2_coded == "health" | beruf2_coded == "housework" | beruf2_coded == "journalism" | beruf2_coded == "member of parliament" | beruf2_coded == "military" | beruf2_coded == "performing arts" | beruf2_coded == "social work" | beruf2_coded == "union" | beruf2_coded == "unskilled worker"
replace beruf_coded = "engineer" if beruf_coded == "engineering"
replace beruf2_coded = "engineer" if beruf2_coded == "engineering"
replace unifach = "other" if unifach == "Computer science" | unifach == "Forstwirtschaft"  | unifach == "Theologie"  | unifach == "history"  | unifach == "political science"

*generate dummies
tab unifach, gen(subject_)
tab bildung_coded, gen(abschluss_)
tab bildung2_coded, gen(fhuni_)
tab beruf_coded, gen(job_)
tab beruf2_coded, gen(indbank_)

*calculate mayor age
split geburtsdatum ,g(part) p(".")
replace part3=part1 if part3==""
destring part3, gen(gebjahr)
gen alter=year - gebjahr if gebjahr!=.

*create dummies for party affiliation
gen cdu_traeger=(mayor_party=="CDU")
gen spd_traeger=(mayor_party=="SPD")
gen independent_traeger=(mayor_party=="None")
replace independent_traeger = 1 if mayor_party=="FWG" | mayor_party=="B𲧥rblock"  | mayor_party=="B𲧥rb𮤮is" | mayor_party=="B𲧥rforum" | mayor_party=="FW" | mayor_party=="FWF" | mayor_party=="FWG Buseck" | mayor_party=="FWG B𤩮gen" | mayor_party=="FWL"

*rename dummies
rename abschluss_1 Abitur
rename abschluss_2 Hauptschule
rename abschluss_3 Highereduc
rename abschluss_4 Realschule
rename fhuni_2 FH
rename fhuni_5 Uni
rename subject_11 publicadmin
rename subject_9 law
rename subject_2 bwl
rename subject_5 lehramt
rename subject_4 engineering
rename subject_7 econ
rename job_3 civadmin
rename job_2 business
rename job_6 lawyer
rename job_10 teaching
rename job_5 engineer
rename job_4 craftsman
rename job_8 police
rename job_1 agriculture
rename job_9 research
rename indbank_3 banking
rename indbank_9 industry
rename alter age
drop fhuni_* subject_* job_* indbank_*

*female only for election/appointment years (1 obs per candidate), weiblich in all years 
gen female = weiblich
replace weiblich=weiblich[_n-1] if weiblich==. & weiblich[_n-1]!=.

*generate indicator variable for observations used with full variable set
reg log_Invzuweis_pc direct_elect bmwahl electyr time* bis14jahre_sh ueber65_sh log_Schluess_pc logpop businesstax proptax Realschule Abitur FH Uni business research lawyer teaching engineer craftsman police agriculture weiblich age cdu_traeger spd_traeger independent_traeger, r cluster(code)
gen insample2 = 1 if e(sample)

xtivreg2 log_Invzuweis_pc direct_elect bmwahl electyr time* trend_* bis14jahre_sh ueber65_sh log_Schluess_pc logpop businesstax proptax if insample2 == 1, fe r cluster(code)
est store inv1
estadd local time "YES", replace
estadd local countytr "YES", replace
estadd local mayor "YES", replace
estadd local controls "YES", replace

xtivreg2 log_Invzuweis_pc direct_elect bmwahl electyr time* trend_* bis14jahre_sh ueber65_sh log_Schluess_pc logpop businesstax proptax Realschule Abitur FH Uni if insample2 == 1, fe r cluster(code)
est store inv2
estadd local time "YES", replace
estadd local countytr "YES", replace
estadd local mayor "YES", replace
estadd local controls "YES", replace

xtivreg2 log_Invzuweis_pc direct_elect bmwahl electyr time* trend_* bis14jahre_sh ueber65_sh log_Schluess_pc logpop businesstax proptax Realschule Abitur FH Uni civadmin business research lawyer teaching engineer craftsman police agriculture if insample2 == 1, fe r cluster(code)
est store inv3
estadd local time "YES", replace
estadd local countytr "YES", replace
estadd local mayor "YES", replace
estadd local controls "YES", replace

xtivreg2 log_Invzuweis_pc direct_elect bmwahl electyr time* trend_* bis14jahre_sh ueber65_sh log_Schluess_pc logpop businesstax proptax Realschule Abitur FH Uni civadmin business research lawyer teaching engineer craftsman police agriculture weiblich age  if insample2 == 1, fe r cluster(code)
est store inv4
estadd local time "YES", replace
estadd local countytr "YES", replace
estadd local mayor "YES", replace
estadd local controls "YES", replace

xtivreg2 log_Invzuweis_pc direct_elect bmwahl electyr time* trend_* bis14jahre_sh ueber65_sh log_Schluess_pc logpop businesstax proptax Realschule Abitur FH Uni civadmin business research lawyer teaching engineer craftsman police agriculture weiblich age cdu_traeger spd_traeger independent_traeger if insample2 == 1, fe r cluster(code)
est store inv5
estadd local time "YES", replace
estadd local countytr "YES", replace
estadd local mayor "YES", replace
estadd local controls "YES", replace

estout inv1 inv2 inv3 inv4 inv5 using "$data\table4.tex", ///
style(tex) keep(direct_elect bmwahl electyr weiblich age Realschule Abitur FH Uni civadmin business research lawyer teaching engineer craftsman police agriculture cdu_traeger spd_traeger independent_traeger) mlabels("(I)" "(II)" "(III)" "(IV)" "(V)") order(bmwahl direct_elect electyr weiblich age Realschule Abitur Highereduc business lawyer teaching engineer craftsman police agriculture cdu_traeger spd_traeger independent_traeger) replace title("Electoral cycles in investment transfers from the state-tier by electoral rules for mayors, inclusion of control variables at mayor level") ///
 prehead(\begin{table}[htbp] \scriptsize \hspace{\fill} \resizebox{0.8\textwidth}{!}{\begin{threeparttable}\caption{{\sc @title \label{tab:baseline2}}}\centering\medskip ///
 \begin{tabularx}{0.85\textwidth}{P*{@M}{C}} \toprule )  posthead("") prefoot("\midrule") ///
 varlabels(electyr "Election/appointment year" bmwahl "Elected mayor*Election/appointment year" direct_elect "Elected mayor" log_Schluess_pc "Log of rule-based transfers per capita" bis14jahre_sh "Share of population < 15 years" ueber65_sh "Share of population > 65 years" businesstax "Business tax rate multiplier" proptax "Property tax B rate multiplier" logpop "Log of population size" weiblich "Female mayor" civadmin "Civil administration" age "Age of mayor" Realschule "Realschule degree" Abitur "Abitur degree" Highereduc "Higher education degree" FH "University of applied sciences degree" Uni "University degree" business "Business sector" lawyer "Lawyer" teaching "Teacher" engineer "Engineer" craftsman "Craftsman" police "Police officer" agriculture "Farmer" research "Researcher" cdu_traeger "CDU candidate" spd_traeger "SPD candidate" independent_traeger "Independent candidate") /// 
 stats(time mayor countytr controls N N_g r2 , layout(@ @ @ @ @ @ )  fmt(%~#s %~#s %~#s %~#s %6.0f %6.0f %6.3f ) ///
 labels("Year fixed effects" "Municipality fixed effects" "County-specific time trends" "Control variables" "Observations" "Municipalities" "R-squared" )) /// 
  starlevels(* 0.10 ** 0.05 *** 0.01) ///
 cells(b(star fmt(%8.3f) ) se(par fmt(%6.3f))     ) ///
 postfoot(\bottomrule\end{tabularx}\begin{tablenotes} \scriptsize{Notes: The dependent variable is the log of investment transfers per capita. Standard errors are robust to heteroscedasticity and clustered at the municipality-level (reported in parentheses). Control variables include share of population over 65 years, share of population below 15 years, log of population size, log of rule-based transfers per capita, and business and property tax rate multipliers. Stars indicate significance levels at 10\% (*), 5\% (**) and 1\%(***).}\end{tablenotes} \end{threeparttable}} \hspace*{\fill}\end{table}) notype

  
*+++++++++++++++++++++++++++++++++++++++
*TABLE A.3 MAYOR CHARACTERISTICS t-test+
*+++++++++++++++++++++++++++++++++++++++
gen cdu = cdu_traeger
gen spd = spd_traeger
gen independent = independent_traeger

replace cdu = . if gewaehlt_new ==""
replace spd = . if gewaehlt_new ==""
replace independent = . if gewaehlt_new ==""

gen drop = 1 if cdu_traeger == 0 & spd_traeger == 0 & independent_traeger == 0
replace cdu_traeger =. if drop == 1
replace spd_traeger =. if drop == 1
replace independent_traeger =. if drop == 1
drop drop cdu spd independent

foreach c of varlist Hauptschule Realschule Abitur Highereduc FH Uni publicadmin law bwl lehramt engineering econ age civadmin business lawyer teaching engineer craftsman police agriculture research banking industry female cdu_traeger spd_traeger independent_traeger{
replace `c' = . if wahlregel ==""
	}

foreach c of varlist Hauptschule Realschule Abitur Highereduc FH Uni publicadmin law bwl lehramt engineering econ age civadmin business lawyer teaching engineer craftsman police agriculture research banking industry female cdu_traeger spd_traeger independent_traeger{
	di   `"`c'"'
ttest `c', by(direct_elect)
estpost ttest `c', by(direct_elect)
matrix x_`c'=(e(mu_1),e(N_1) \ e(mu_2),e(N_2) \ e(b),e(count) \ e(t) ,.)
	}

matrix full_1 = (x_Hauptschule, x_Realschule, x_Abitur, x_Highereduc)
matrix list full_1

matrix full_2 = (x_FH, x_Uni, x_publicadmin, x_law)
matrix list full_2

matrix full_3 = (x_bwl, x_lehramt, x_engineering, x_econ)
matrix list full_3

matrix full_4 = (x_civadmin, x_lawyer, x_business, x_banking)
matrix list full_4

matrix full_5 = (x_industry, x_teaching, x_engineer, x_craftsman)
matrix list full_5

matrix full_6 = (x_police, x_agriculture, x_research, x_independent_traeger)
matrix list full_6

matrix full_7 = (x_cdu_traeger, x_spd_traeger, x_female, x_age)
matrix list full_7

exit
